﻿CREATE PROCEDURE [NoteManagement].[SaveCaseNote]
@OID		bigint				= NULL,
@Version	rowversion			= NULL,
@CaseID	    bigint				= NULL,
@Notes		varchar(MAX)		= NULL,
@AuthorID   uniqueidentifier	= NULL

AS 

---------------------------------------------------------------------------------------------------
--						Liberty
---------------------------------------------------------------------------------------------------
--  Stored Procedure :: SaveCaseNote
--                                                                            
---------------------------------------------------------------------------------------------------

SET NOCOUNT ON 

DECLARE @newOID bigint

---------------------------------------------------------------------------------------------------
-- Step 1: Ensure the Case exist
---------------------------------------------------------------------------------------------------

IF NOT EXISTS(SELECT 1 FROM dbo.Cases WHERE OID = @CaseID)
BEGIN
	RAISERROR('Case does not exist', 16, 1)
	GOTO ExitError
END

---------------------------------------------------------------------------------------------------
-- Step 3: "Save" the case note.
---------------------------------------------------------------------------------------------------
-- If no OID was passed in, this is a new Note; perform an insert.
IF ISNULL(@OID, 0) <= 0
BEGIN
	INSERT INTO dbo.CaseNotes
		(CaseID, Notes, AuthorID, UpdatedDate, CreatedDate)
	VALUES
		(@CaseID, @Notes, @AuthorID, GETDATE(), GETDATE())

	SET @OID =  SCOPE_IDENTITY()

	-- 1.3. Retutn OID and timestamp of newly inserted object
	SELECT @Version = Version 
	FROM dbo.CaseNotes 
	WHERE OID = @OID
END

-- If OID was passed in, perform an update
ELSE
BEGIN
	-- Attempt updating object (using both OID and timestamp for concurrency reasons)
	UPDATE dbo.CaseNotes
	SET CaseID = @CaseID
		,Notes = @Notes
		,AuthorID = @AuthorID
		,UpdatedDate = GETDATE()
	WHERE (OID = @OID) 
	AND (Version = @Version)

	-- If no records were found (and updated) - it means that another process had updated 
	-- this record (therefore timestamp will be different) or record was deleted (OID not found)
	-- Either way, we need to raise exception
	IF @@ROWCOUNT = 0
	BEGIN
		RAISERROR(99999, 16, 1, 'CaseNotes')
	END

	-- Record was found - return OID and new timestamp
	ELSE
	BEGIN 
		SELECT @Version = Version 
		FROM dbo.CaseNotes
		WHERE OID = @OID
	END
END

---------------------------------------------------------------------------------------------------
-- Step 5: Return all the associated values to the caller.
---------------------------------------------------------------------------------------------------

SELECT @OID as 'OID', @Version as 'Version'

RETURN(0)

ExitError:
	RETURN(1)


